package com.scalar.chiptrack.operations.discrepancyLotsNotifications.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.discrepancyLotsNotifications.DiscrepancyLot;

/**
 * Created by IntelliJ IDEA.
 * User: Murali Krishna Yengala
 * Date: Sep 12, 2005
 * Time: 6:39:48 PM
 * To change this template use File | Settings | File Templates.
 */
public class DiscrepancyLotsNotificationsDAO {

    public static HashMap loadDiscrepancyLotsByStatus( String status) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        HashMap discrepancyLotsMap = new LinkedHashMap();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ( "SELECT NOTIFICATION_ID, LOT_ID, LOT_NUMBER, NOTIFICATION_TYPE,PREV_PROCESS ,PROCESS, PREV_WIP_QTY, WIP_QTY, VENDOR, LOCATION, WAFER_ID, SHIP_QTY, Convert(Varchar, TimeStamp, 101) as NOTIFICATION_DATE  FROM LOT_NOTIFICATION_TABLE WHERE STATUS LIKE ? ");
            pstmt.setString(1, status );
            rset = pstmt.executeQuery();
            ArrayList lotsList;
            while (rset.next()) {
                DiscrepancyLot discrepancyLot = new DiscrepancyLot();
                discrepancyLot.setNotificationId( rset.getInt( "NOTIFICATION_ID"));
                discrepancyLot.setLot_id( rset.getInt("LOT_ID"));
                discrepancyLot.setLotNumber( rset.getString( "LOT_NUMBER"));
                discrepancyLot.setNotificationType( rset.getString("NOTIFICATION_TYPE"));
                discrepancyLot.setPrevProcess( rset.getString("PREV_PROCESS"));
                discrepancyLot.setCurrentProcess( rset.getString("PROCESS"));
                discrepancyLot.setPrevQty( rset.getString("PREV_WIP_QTY"));
                discrepancyLot.setCurrentQty( rset.getString("WIP_QTY"));
                discrepancyLot.setVendor( rset.getString("VENDOR"));
                discrepancyLot.setLocation( rset.getString("LOCATION"));
                discrepancyLot.setWaferId( rset.getString("WAFER_ID"));
                discrepancyLot.setShipQty( rset.getString("SHIP_QTY"));
                discrepancyLot.setNotificationDate( rset.getString("NOTIFICATION_DATE") );
                lotsList = (ArrayList) discrepancyLotsMap.get (discrepancyLot.getNotificationType());
                if (lotsList == null)
                {
                    lotsList = new ArrayList();
                }
                lotsList.add (discrepancyLot);
                discrepancyLotsMap.put (discrepancyLot.getNotificationType(), lotsList);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Error loading " + status + " Discrepancy Lots.");
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
        }

        return discrepancyLotsMap;
    }

    public static ArrayList loadSelectedDiscrepancyLots(String[] notificationIds) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList discrepancyLotsList = new ArrayList();
        StringBuffer sqlQuery = new StringBuffer("select Notification_Id, Lot_Id, Lot_Number, Notification_Type from Lot_Notification_Table where Notification_id in (");

        try
        {
                for( int i = 0; i < notificationIds.length; i++  )
                {
                    int notificationIdInt;
                    String notificationIdStr = notificationIds[i];
                    notificationIdStr = notificationIdStr == null ? "" : notificationIdStr.trim();
                    try
                    {
                        notificationIdInt = Integer.parseInt(notificationIdStr);
                    }
                    catch( NumberFormatException e)
                    {
                        e.printStackTrace();
                        continue;
                    }
                    sqlQuery.append(notificationIdInt);
                    if( i+1 != notificationIds.length )
                    {
                        sqlQuery.append(", ");
                    }
                }
                sqlQuery.append(")  order by timestamp");
                System.out.println("sqlQuery : " + sqlQuery.toString());
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement ( sqlQuery.toString() );
                rset = pstmt.executeQuery();
                ArrayList lotsList;
                while (rset.next())
                {
                    DiscrepancyLot discrepancyLot = new DiscrepancyLot();
                    discrepancyLot.setNotificationId( rset.getInt( 1));
                    discrepancyLot.setLot_id( rset.getInt( 2));
                    discrepancyLot.setLotNumber( rset.getString( 3));
                    discrepancyLot.setNotificationType( rset.getString(4));
                    discrepancyLotsList.add( discrepancyLot );
                }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Error loading Discrepancy Lots");
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){ ignore.printStackTrace();}
            }
        }

        return discrepancyLotsList;
    }

    public static void updateNotificationsStatus(String[] notificationIds ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "update Lot_Notification_Table set status = ? where Notification_Id = ?" );

            if( notificationIds != null && notificationIds.length > 0)
            {
                for( int i = 0; i < notificationIds.length; i++)
                {
                    String notificationIdStr = notificationIds[i];
                    notificationIdStr = notificationIdStr == null ? "" : notificationIdStr.trim();
                    int notificationId;
                    try
                    {
                        notificationId = Integer.parseInt(notificationIdStr);
                    }
                    catch( NumberFormatException ne)
                    {
                        ne.printStackTrace();
                        continue;
                    }
                    pstmt.setString(1, Constants.STATUS_CLOSE );
                    pstmt.setInt(2, notificationId );
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Error updating Discrepancy Lots");
        }
        finally
        {
            try
            {
                if (conn != null)
                {
                    if ( success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore)
            {
                ignore.printStackTrace();
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore) { ignore.printStackTrace();}
            }
            if( conn != null )
            {
                try { conn.close(); }  catch (SQLException ignore) { ignore.printStackTrace();}
            }
        }
    }
}
